package student.aistar.dao.impl;

import student.aistar.dao.ITVideoDao;
import student.aistar.dao.IUserDao;
import student.aistar.entity.TVideo;
import student.aistar.entity.User;
import student.aistar.util.JdbcTemplate;
import student.aistar.util.JdbcUtil;
import student.aistar.util.PoolUtil;
import student.aistar.util.PreparedStatementCallBack;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

public class TVideoDaoImpl implements ITVideoDao {
    IUserDao userDao = new UserDaoImpl();
    @Override
    public List<TVideo> findAll() {
        List<TVideo> list = new ArrayList<>();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from t_video";
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                TVideo tVideo = new TVideo();
                tVideo.setId(resultSet.getInt(1));
                tVideo.setTitle(resultSet.getString(2));
                tVideo.setCreateDate(resultSet.getDate(3));
                tVideo.setPrice(resultSet.getDouble(4));
                tVideo.setVideoUrl(resultSet.getString(5));
                User user = userDao.getById(resultSet.getInt(6));
                tVideo.setUser(user);

                list.add(tVideo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                JdbcUtil.close(connection, statement, resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }

    @Override
    public int delById(int id) {
        Connection connection = null;
        Statement statement = null;
        int flag = 0;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "delete from t_video where id=" + id;
            System.out.println(sql);
            flag = statement.executeUpdate(sql);
            if(flag > 0){
                System.out.println("删除成功");
            }
            else
                System.out.println("删除失败");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtil.close(connection, statement);
        }
        return flag;
    }

    @Override
    public int update(TVideo tVideo) {
        Connection connection = null;
        Statement statement = null;
        int flag = 0;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "update t_video set title = '" + tVideo.getTitle() + "', create_date='" + tVideo.getCreateDate()
                    + "', price=" + tVideo.getPrice() + ", video_url='" + tVideo.getVideoUrl() + "', user_id=" + tVideo.getUser().getId()
                    + " where id = " + tVideo.getId();
            System.out.println(sql);
            flag = statement.executeUpdate(sql);
            if(flag > 0)
                System.out.println("更新成功");
            else
                System.out.println("更新失败");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtil.close(connection, statement);
        }

        return flag;
    }

    @Override
    public TVideo getId(int id) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        TVideo tVideo = new TVideo();
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();
            String sql = "select * from t_video where id = " + id;
            System.out.println(sql);
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                tVideo.setId(resultSet.getInt(1));
                tVideo.setTitle(resultSet.getString(2));
                tVideo.setCreateDate(resultSet.getDate(3));
                tVideo.setPrice(resultSet.getDouble(4));
                tVideo.setVideoUrl(resultSet.getString(5));
                User user = userDao.getById(resultSet.getInt(6));
                tVideo.setUser(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return tVideo;
    }

    @Override
    public int insert(TVideo tVideo) {
        Connection connection = null;
//        Statement statement = null;
        PreparedStatement preparedStatement = null;
        int flag = 0;
        try {
            connection = JdbcUtil.getConnection();
            String sql = "insert into t_video(title,create_date,price,video_url,user_id) values(?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1, "awdawd");
            preparedStatement.setObject(2, new java.sql.Date(tVideo.getCreateDate().getTime()));
            preparedStatement.setObject(3, 100.0);
            preparedStatement.setObject(4, "eqwew");
            preparedStatement.setObject(5, tVideo.getUser().getId());
//            statement = connection.createStatement();
//            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
//            String format = simpleDateFormat.format(tVideo.getCreateDate());
//            String sql = "insert into t_video(title,create_date,price,video_url,user_id) values ('"+tVideo.getTitle()+"','"+format+"','"+tVideo.getPrice()+"','"+tVideo.getVideoUrl()+"','"+tVideo.getUser().getId()+"')";
//            System.out.println(sql);
//            flag = statement.executeUpdate(sql);
            flag = preparedStatement.executeUpdate();

            if(flag > 0)
                System.out.println("插入成功");
            else
                System.out.println("插入失败");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtil.close(connection, preparedStatement);
        }

        return flag;
    }

    @Override
    public int saveList(List<TVideo> videoList) {

        return 0;
    }

    @Override
    public void saveListBatch(List<TVideo> videoList) {

    }

    @Override
    public void delByIdTemplate(int id) throws SQLException {
        JdbcTemplate.execute(new PreparedStatementCallBack() {
            @Override
            public PreparedStatement execute(Connection connection) throws SQLException {
                String sql = "delete from t_video where id=" + id;
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                return preparedStatement;
            }
        });
    }

    @Override
    public List<TVideo> findPage(String username, String title, Integer pageNow, Integer pageSize) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<TVideo> videos = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            String sql = "select t_video.* from user right join t_video on t_video.user_id = user.id where 1=1 ";
            List<Object> list = new ArrayList<>();
            if(null!=username && username.trim().length()>0){
                sql += " and username like concat('%',?,'%')";
                list.add(username);
            }
            if(title != null){
                sql += " and title like concat('%',?,'%')";
                list.add(title);
            }
            pageNow = (pageNow - 1) * pageSize;
            sql +=" limit ?, ?";
            list.add(pageNow);
            list.add(pageSize);
            preparedStatement = connection.prepareStatement(sql);
            if(list.size() > 0){
                for (int i = 0; i < list.size(); i++) {
                    preparedStatement.setObject(i + 1, list.get(i));
                }
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                TVideo tVideo = new TVideo();
                tVideo.setId(resultSet.getInt(1));
                tVideo.setTitle(resultSet.getString(2));
                tVideo.setCreateDate(resultSet.getDate(3));
                tVideo.setPrice(resultSet.getDouble(4));
                tVideo.setVideoUrl(resultSet.getString(5));

                videos.add(tVideo);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return videos;
    }

    @Override
    public int countVideos(String username, String title) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<TVideo> videos = new ArrayList<>();
        try {
            connection = JdbcUtil.getConnection();
            String sql = "select count(*) from user right join t_video on t_video.user_id = user.id where 1=1 ";
            List<Object> list = new ArrayList<>();
            if(null!=username && username.trim().length()>0){
                sql += " and username like concat('%',?,'%')";
                list.add(username);
            }
            if(title != null){
                sql += " and title like concat('%',?,'%')";
                list.add(title);
            }
            preparedStatement = connection.prepareStatement(sql);
            if(list.size() > 0){
                for (int i = 0; i < list.size(); i++) {
                    preparedStatement.setObject(i + 1, list.get(i));
                }
            }
            resultSet = preparedStatement.executeQuery();
            if(resultSet.next())
                return resultSet.getInt(1);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}
